﻿using Dapper;
using GLstudent.Models;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace GLstudent.DAL
{
    public class DAL_guanliyuan
    {
        /// <summary>
        /// 查询班级表所有信息
        /// </summary>
        /// <returns></returns>
        public List<banji> Select()
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var list = mySqlConnection.Query<banji>("SELECT * FROM banji ").ToList();
            return list;
        }
        /// <summary>
        /// 按ID查询班级
        /// </summary>
        /// <param name="banjiid">班级id</param>
        /// <returns></returns>
        public chakanbanji chaxunid(int banjiid)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var chaxunid = mySqlConnection.QueryFirstOrDefault<chakanbanji>("SELECT banji.id AS banjiid,banji.b_name AS banjiname,banji.t_bianhao AS banzhurenbianhao,teacher.t_name AS banzhuren FROM banji JOIN teacher ON banji.t_bianhao = teacher.t_bianhao WHERE banji.id = @banjiid ", new { banjiid });
            return chaxunid;
        }
        /// <summary>
        /// 模糊查询班级的方法
        /// </summary>
        /// <param name="mohu">模糊查询输入的字符</param>
        /// <returns></returns>
        public List<banji> mohu(string mohu)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var list = mySqlConnection.Query<banji>
                ($"SELECT * FROM banji WHERE b_name LIKE  '%{mohu}%' ", new { mohu }).ToList();
            return list;
        }
        /// <summary>
        /// 增加班级的方法
        /// </summary>
        /// <param name="zengjia">与banji类对应的参数</param>
        /// <returns></returns>
        public int Insert(banji zengjia)
        {
            var sql = "insert into banji (b_name,t_bianhao) values (@b_name,@t_bianhao)";
            MySqlConnection mySqlConnection = new MySqlConnection
            (ConfigHelper.Configuration["MySQLConnectionString"]);
            var zengjia1 = mySqlConnection.Execute(sql, zengjia);
            return zengjia1;
        }
        /// <summary>
        /// 删除班级的方法
        /// </summary>
        /// <param name="id">要删除的id</param>
        /// <returns></returns>
        public int Delete(int id)
        {
            var sql = "DELETE FROM banji WHERE id = @id";
            MySqlConnection mySqlConnection = new MySqlConnection
                (ConfigHelper.Configuration["MySQLConnectionString"]);
            var shanchu = mySqlConnection.Execute(sql, new { id });
            return shanchu;
        }
        /// <summary>
        /// 更改班级的方法
        /// </summary>
        /// <param name="g">与banji类对应的参数</param>
        /// <returns></returns>
        public int Update(banji g)
        {
            var sql = "UPDATE banji set b_name=@b_name, t_bianhao=@t_bianhao WHERE id= @id";
            MySqlConnection mySqlConnection = new MySqlConnection
                (ConfigHelper.Configuration["MySQLConnectionString"]);
            var genggai = mySqlConnection.Execute(sql, g);
            return genggai;
        }
        /// <summary>
        /// 查询课程表所有信息
        /// </summary>
        /// <returns></returns>
        public List<kecheng> Selectkecheng()
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var list = mySqlConnection.Query<kecheng>("SELECT * FROM kecheng ").ToList();
            return list;
        }
        /// <summary>
        /// 模糊查询课程的方法
        /// </summary>
        /// <param name="mohu">模糊查询输入的字符</param>
        /// <returns></returns>
        public List<kecheng> mohukecheng(string mohu)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var list = mySqlConnection.Query<kecheng>
                ($"SELECT * FROM kecheng WHERE k_name LIKE  '%{mohu}%' ", new { mohu }).ToList();
            return list;
        }
        /// <summary>
        /// 按ID查询课程
        /// </summary>
        /// <param name="kechengid">课程id</param>
        /// <returns></returns>
        public chakankecheng chaxunkechengid(int kechengid)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var chaxunid = mySqlConnection.QueryFirstOrDefault<chakankecheng>("SELECT kecheng.id AS kechengid,kecheng.k_name AS kcname,kecheng.t_bianhao AS renkejiaoshi,teacher.t_name AS jiaoshiname FROM kecheng JOIN teacher ON kecheng.t_bianhao = teacher.t_bianhao WHERE kecheng.id  = @kechengid ", new { kechengid });
            return chaxunid;
        }
        /// <summary>
        /// 增加课程的方法
        /// </summary>
        /// <param name="zengjia">与kecheng类对应的参数</param>
        /// <returns></returns>
        public int Insertkecheng(kecheng zengjia)
        {
            var sql = "insert into kecheng (k_name,t_bianhao) values (@k_name,@t_bianhao)";
            MySqlConnection mySqlConnection = new MySqlConnection
            (ConfigHelper.Configuration["MySQLConnectionString"]);
            var zengjia1 = mySqlConnection.Execute(sql, zengjia);
            return zengjia1;
        }
        /// <summary>
        /// 删除课程的方法
        /// </summary>
        /// <param name="id">要删除的id</param>
        /// <returns></returns>
        public int Deletekecheng(int id)
        {
            var sql = "DELETE FROM kecheng WHERE id = @id";
            MySqlConnection mySqlConnection = new MySqlConnection
                (ConfigHelper.Configuration["MySQLConnectionString"]);
            var shanchu = mySqlConnection.Execute(sql, new { id });
            return shanchu;
        }
        /// <summary>
        /// 更改课程的方法
        /// </summary>
        /// <param name="g">与kecheng类对应的参数</param>
        /// <returns></returns>
        public int Updatekecheng(kecheng g)
        {
            var sql = "UPDATE kecheng set k_name=@k_name, t_bianhao=@t_bianhao WHERE id= @id";
            MySqlConnection mySqlConnection = new MySqlConnection
                (ConfigHelper.Configuration["MySQLConnectionString"]);
            var genggai = mySqlConnection.Execute(sql, g);
            return genggai;
        }
        /// <summary>
        /// 查询课教师所有信息
        /// </summary>
        /// <returns></returns>
        public List<teacher> Selectjiaoshi()
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var list = mySqlConnection.Query<teacher>("SELECT * FROM teacher ").ToList();
            return list;
        }
        /// <summary>
        /// 模糊查询教师的方法
        /// </summary>
        /// <param name="mohu">模糊查询输入的字符</param>
        /// <returns></returns>
        public List<teacher> mohujiaoshi(string mohu)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var list = mySqlConnection.Query<teacher>
                ($"SELECT * FROM teacher WHERE t_name LIKE  '%{mohu}%' ", new { mohu }).ToList();
            return list;
        }
        /// <summary>
        /// 按ID查询教师
        /// </summary>
        /// <param name="id">教师id</param>
        /// <returns></returns>
        public teacher chaxunjiaoshiid(int id)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var chaxunid = mySqlConnection.QueryFirstOrDefault<teacher>("SELECT * FROM teacher WHERE id  = @id ", new { id });
            return chaxunid;
        }
        /// <summary>
        /// 增加班级的方法
        /// </summary>
        /// <param name="zengjia">与teacher类对应的参数</param>
        /// <returns></returns>
        public int Insertjiaoshi(teacher zengjia)
        {
            var sql = "insert into teacher (t_name,t_sex,t_lianxi,t_age,t_bianhao) values (@t_name,@t_sex,@t_lianxi,@t_age,@t_bianhao)";
            MySqlConnection mySqlConnection = new MySqlConnection
            (ConfigHelper.Configuration["MySQLConnectionString"]);
            var zengjia1 = mySqlConnection.Execute(sql, zengjia);
            return zengjia1;
        }
        /// <summary>
        /// 删除课程的方法
        /// </summary>
        /// <param name="id">要删除的id</param>
        /// <returns></returns>
        public int Deletejiaoshi(int id)
        {
            var sql = "DELETE FROM teacher WHERE id = @id";
            MySqlConnection mySqlConnection = new MySqlConnection
                (ConfigHelper.Configuration["MySQLConnectionString"]);
            var shanchu = mySqlConnection.Execute(sql, new { id });
            return shanchu;
        }
        /// <summary>
        /// 更改教师的方法
        /// </summary>
        /// <param name="g">与teacher类对应的参数</param>
        /// <returns></returns>
        public int Updatejiaoshi(teacher g)
        {
            var sql = "UPDATE teacher set t_name=@t_name, t_sex=@t_sex, t_lianxi=@t_lianxi, t_age=@t_age, t_bianhao=@t_bianhao WHERE id= @id";
            MySqlConnection mySqlConnection = new MySqlConnection
                (ConfigHelper.Configuration["MySQLConnectionString"]);
            var genggai = mySqlConnection.Execute(sql, g);
            return genggai;
        }
        /// <summary>
        /// 查询登录表所有信息的方法
        /// </summary>
        /// <returns></returns>
        public List<denglu> Selectdenglu()
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var list = mySqlConnection.Query<denglu>("SELECT * FROM denglu ").ToList();
            return list;
        }
        /// <summary>
        /// 模糊查询登陆表的方法
        /// </summary>
        /// <param name="mohu">模糊查询输入的字符</param>
        /// <returns></returns>
        public List<denglu> mohudenglu(string mohu)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var list = mySqlConnection.Query<denglu>
                ($"SELECT * FROM denglu WHERE yonghu LIKE  '%{mohu}%' ", new { mohu }).ToList();
            return list;
        }
        /// <summary>
        /// 按教师编号查询视图信息
        /// </summary>
        /// <param name="jiaoshibianhao">教师编号</param>
        /// <returns></returns>
        public denglubiaojiaoshishitu chaxunbianhao(string jiaoshibianhao)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var chaxunid = mySqlConnection.QueryFirstOrDefault<denglubiaojiaoshishitu>(" SELECT denglu.id AS dengluid,denglu.yonghu AS yonghuming,denglu.mima AS yonghumima, denglu.t_bianhao AS jiaoshibianhao, teacher.t_name AS jiaoshiname, denglu.guanlizhe AS guanliyuan FROM denglu JOIN teacher ON denglu.t_bianhao = teacher.t_bianhao WHERE denglu.t_bianhao = @jiaoshibianhao ", new { jiaoshibianhao });
            return chaxunid;
        }
        /// <summary>
        /// 按学生学号查询视图信息
        /// </summary>
        /// <param name="xueshengxuehao">学生学号</param>
        /// <returns></returns>
        public denglubiaoxuesheng chaxunxuehao(string xueshengxuehao)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var chaxunid = mySqlConnection.QueryFirstOrDefault<denglubiaoxuesheng>(" SELECT denglu.id AS dengluid,denglu.yonghu AS yonghuming,denglu.mima AS yonghumima, denglu.s_xuehao AS xueshengxuehao, student.s_name AS xueshengname FROM denglu JOIN student	ON denglu.s_xuehao = student.s_xuehao WHERE denglu.s_xuehao = @xueshengxuehao ", new { xueshengxuehao });
            return chaxunid;
        }
        /// <summary>
        /// 删除登录信息的方法
        /// </summary>
        /// <param name="id">要删除的id</param>
        /// <returns></returns>
        public int Deletedenglu(int id)
        {
            var sql = "DELETE FROM denglu WHERE id = @id";
            MySqlConnection mySqlConnection = new MySqlConnection
                (ConfigHelper.Configuration["MySQLConnectionString"]);
            var shanchu = mySqlConnection.Execute(sql, new { id });
            return shanchu;
        }
        /// <summary>
        /// 更改登录信息的方法
        /// </summary>
        /// <param name="g">与denglu类对应的参数</param>
        /// <returns></returns>
        public int Updatedenglu(denglu g)
        {
            var sql = "UPDATE denglu set yonghu=@yonghu, mima=@mima, t_bianhao=@t_bianhao, s_xuehao=@s_xuehao, guanlizhe=@guanlizhe WHERE id= @id";
            MySqlConnection mySqlConnection = new MySqlConnection
                (ConfigHelper.Configuration["MySQLConnectionString"]);
            var genggai = mySqlConnection.Execute(sql, g);
            return genggai;
        }
        /// <summary>
        /// 查询所有学生信息并按照班级名称排序
        /// </summary>
        /// <returns></returns>
        public List<student> chaxunsuoyou()
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var chaxunid = mySqlConnection.Query<student>("SELECT * FROM student ORDER BY b_name ").ToList();
            return chaxunid;
        }
        /// <summary>
        /// 模糊查询学生表信息
        /// </summary>
        /// <param name="mohu">模糊查询的字符</param>
        /// <returns></returns>
        public List<student> mohuxuesheng(string mohu)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var list = mySqlConnection.Query<student>
                ($"SELECT * FROM student WHERE  s_name LIKE  '%{mohu}%'  ", new { mohu }).ToList();
            return list;
        }
        /// <summary>
        /// 查询学生表内是否有该班的学生
        /// </summary>
        /// <param name="bname">班级名称</param>
        /// <returns></returns>
        public List<student> chaxunbanjixuesheng(string bname)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var list = mySqlConnection.Query<student>
                ("SELECT * FROM student WHERE b_name = @bname", new { bname }).ToList();
            return list;
        }
        /// <summary>
        /// 查询教师是否是班主任
        /// </summary>
        /// <param name="tbianhao">教师编号</param>
        /// <returns></returns>
        public List<banji> chaxunbanzhuren(string tbianhao)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var list = mySqlConnection.Query<banji>
                ("SELECT * FROM banji WHERE t_bianhao = @tbianhao", new { tbianhao }).ToList();
            return list;
        }
        /// <summary>
        /// 查询班级表中是否存在该班级
        /// </summary>
        /// <param name="bname">班级名称</param>
        /// <returns></returns>
        public List<banji> chaxunbanji(string bname)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var list = mySqlConnection.Query<banji>
                ("SELECT * FROM banji WHERE b_name = @bname", new { bname }).ToList();
            return list;
        }
        /// <summary>
        /// 查询该科目下是否有学生学习
        /// </summary>
        /// <param name="kname">课程名称</param>
        /// <returns></returns>
        public List<chaxunkemu> chaxunkemu(string kname)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var list = mySqlConnection.Query<chaxunkemu>
                ("SELECT kecheng.k_name AS kname,chengji.s_xuehao AS sxuehao FROM chengji JOIN kecheng ON kecheng.id = chengji.kecheng_id WHERE kecheng.k_name = @kname GROUP BY kecheng.k_name,chengji.s_xuehao", new { kname }).ToList();
            return list;
        }
    }
}
